<?php
require_once 'Translator/Model.php';

/**
 * Bundle model.
 */
class BundleModel extends Translator_Model {
	/**
	 * Fetch bundles and their stats in the passed language.
	 * 
	 * @param int $language ID of language
	 */
	public function fetchBundlesAndStats($languageid) {
		return $this->_db->fetchAll(
			'SELECT bundles.*, stats.translated
			 FROM bundles
			 LEFT OUTER JOIN bundle_stats AS stats
			 	ON(stats.language = ? AND bundles.id = stats.bundle)',
			array($languageid)
		);
	}
	
	public function fetchBundles() {
		return $this->_db->fetchAll('SELECT * FROM bundles');
	}
	
	/**
	 * Fetch bundle by id.
	 */
	public function fetchBundle($id) {
		return $this->_db->fetchRow(
			'SELECT * FROM bundles WHERE id = ? LIMIT 1',
			array($id)
		);
	}
	
	/**
	 * Insert bundle.
	 */
	public function insertBundle($data) {
		$this->_db->insert('bundles',$data);		
		return $this->_db->lastInsertId();
	}
	
	
	/**
	 * Fetch keys belonging to the given bundle. If second parameter
	 * has been passed as well the translations for the keys are loaded
	 * as well.
	 * 
	 * @param int $bundleId
	 * @param int $languageid language id to fetch translations of as well
	 * @param boolean $excludetranslated If selected, translated keys are not returned
	 */
	public function fetchKeys($bundleId, $languageid = null, $excludetranslated = false) {
		if($languageid == null) {
			return $this->_db->fetchAll(
				'SELECT * FROM `keys` WHERE bundle = ? ORDER BY name ASC',
				array($bundleId)
			);
		} else {
			return $this->_db->fetchAll(
				'SELECT `keys`.*, trans.translation, trans.translated
				 FROM `keys`
				 LEFT JOIN key_translations AS trans
				 	ON(trans.`key` = `keys`.id AND trans.language = ?)
				 WHERE `keys`.bundle = ? '.($excludetranslated ? 'AND trans.translated = 0' : '').'
				 ORDER BY name ASC',
				array($languageid, $bundleId)
			);
		}
	}
	
	/**
	 * Toggle translation status of the given key in the passed language.
	 * 
	 * @param $keyId
	 * @param $languageId
	 */
	public function toggleKeyState($keyId, $languageId) {
		$translation = $this->_db->fetchRow(
			'SELECT trans.id, trans.translated, `keys`.bundle
			 FROM key_translations AS trans
			 RIGHT JOIN `keys` ON(`keys`.id = trans.key)
			 WHERE trans.key = ? AND trans.language = ?',
			array($keyId, $languageId)
		);
		
		if(empty($translation)) {
			return false;
		}
		
		$translated = $translation->translated == 1;
		
		// update stats
		$this->_db->update(
			'key_translations',
			array('translated' => !$translated),
			'id = '.intval($translation->id)
		);
		
		$this->_db->update(
			'bundle_stats',
			array('translated' => new Zend_Db_Expr('translated'.($translated ? '-1' : '+1'))),
			'language = '.intval($languageId).' AND bundle = '.$translation->bundle
		);
		
		$totalKeys = $this->_db->fetchOne('SELECT SUM(count) FROM bundles');
	
		$totalTranslated = $this->_db->fetchOne(
			'SELECT SUM(translated) 
			 FROM bundle_stats 
			 WHERE language = ?',
			array($languageId));
			
		$this->_db->update(
			'languages',
			array(
				'progress' => (float) $totalTranslated / $totalKeys
			), 
			'id = '.intval($languageId)
		);
		
		return !$translated;
	}
	
	/**
	 * Insert keys into the database. Returns another array where the orignal
	 * text has been replaced with the ID of the entry in the database.
	 * This is handy for translations which have to be created as well.
	 * 
	 * Adds the number of added key to count in the bundle table. 
	 * {@see updateBundleStats()} to update statistics for languages as well.
	 * 
	 * @param $bundleId
	 * @param $keys associative array with keys
	 * @return array 
	 */
	public function insertKeys($bundleId, $keys) {
		$return = array();
		
		foreach($keys as $key => $value) {
			$this->_db->insert(
				'keys',
				array('bundle' => $bundleId, 'name' => $key, 'original' => $value)
			);
			
			$return[$key] = $this->_db->lastInsertId();
		}
		
		// update bundle key count
		$this->_db->query(
			'UPDATE bundles 
			 SET count = count + '.count($keys).'
			 WHERE id = '.intval($bundleId));
		
		return $return;
	}
	
	/**
	 * Insert translations of a bundle into the database.
	 * 
	 * Does not update the statistics, please use {@see updateBundleStats()}
	 * for that.
	 * 
	 * @param int $languageId language id this translation belongs to
	 * @param array $keys
	 */
	public function insertTranslation($languageId, $keys) {
		foreach($keys as $key => $array) {
			// translation necessary
			if(is_null($array['value'])) {
				$this->_db->insert('key_translations',
					array(
						'key' => $array['id'], 
						'language' => $languageId, 
						'translated' => '0'
					)
				);
			} else {
				$this->_db->insert('key_translations',
					array(
						'key' => $array['id'], 
						'language' => $languageId, 
						'translation' => $array['value'],
						'translated' => '1'
					)
				);
			}
		}
	}
	
	/**
	 * Create stats for a bundle for the passed languages. Also updates
	 * the general translation progress.
	 * 
	 * @param int $bundleId
	 * @param array $languages array with database rows
	 */
	public function createBundleStats($bundleId, $languages) {
		$totalKeys = $this->_db->fetchOne('SELECT SUM(count) FROM bundles');
		
		foreach($languages as $language) {
			$translated = $this->_db->fetchOne(
				'SELECT COUNT(*)
				 FROM key_translations
				 LEFT JOIN `keys` ON `keys`.id = key_translations.`key`
				 WHERE `keys`.bundle = ? AND key_translations.translated = 1',
				array($bundleId));
				
			$this->_db->insert('bundle_stats',
				array(
					'language' => $language->id,
					'bundle' => $bundleId,
					'translated' => $translated
				)
			);
			
			$totalTranslated = $this->_db->fetchOne(
				'SELECT SUM(translated) 
				 FROM bundle_stats 
				 WHERE language = ?',
				array($language->id));
			
			$this->_db->update(
				'languages',
				array(
					'progress' => (float) $totalTranslated / $totalKeys
				), 
				'id = '.intval($language->id)
			);
		}
	}
}